import os
import sys
import time
import re
import logging
t=time.time()
import requests
import json
import logging
from datetime import  datetime
current_time=datetime.now()

from zentao.settings import PYMYSQL_CONF, DIFY_URL
DIFY_URL = 'http://127.0.0.1:8083'

import os
# 设置日志
# ptah="./out_log/"
# if not os.path.exists(ptah):
#     os.mkdir(ptah)
#
# logging.basicConfig(level=logging.DEBUG,
#                     format='%(asctime)s - %(levelname)s - %(message)s',
#                     filename='./out_log/'+current_time.strftime("%Y-%m-%d_%H-%M-%S")+'dawate_log.txt',
#                     filemode='a')  # 追加模式
def generate_type(query):
    #判断是否为生成文件，以及属于哪个生成任务

    if '生成' not in query:
        return '其他'

    # API端点和API密钥
    # url = 'http://172.16.32.45:11206/v1/chat-messages' #v1/chat-messages'调用聊天框  workflows/run智能体
    url = f'{DIFY_URL}/v1/workflows/run' #v1/chat-messages'调用聊天框  workflows/run智能体
    api_key = 'app-gRpMZ0sVPmnQWcVaIsu6X2VL'  # 替换为您的实际API密钥

    # 请求头
    headers = {
        'Authorization': f'Bearer {api_key}',
        'Content-Type': 'application/json'
    }

    # 请求数据
    data = {
        "inputs": {"text": query},
        "response_mode": "blocking",
        "user": 'abc-123'
    }

    # 将数据转换为JSON字符串
    data_json = json.dumps(data)

    # 发送GET请求，并设置stream=True
    response = requests.post(url, headers=headers, data=data_json.encode("utf-8"))
    # print(response.text)
    _type = json.loads(response.text)['data']['outputs']['type']

    return _type

def dify_chat_streaming(query):
    #找表
    # API端点和API密钥
    # url = 'http://172.16.32.45:11206/v1/chat-messages' #v1/chat-messages'调用聊天框  workflows/run智能体
    url = f'{DIFY_URL}/v1/chat-messages' #v1/chat-messages'调用聊天框  workflows/run智能体
    # api_key = 'app-dxPKsfwrQBCK9yN79CBpmfng'  # 替换为您的实际API密钥
    api_key = 'app-oVNa377xjt8YTZxudywZEbZp'  # 工作流查表

    # 请求头
    headers = {
        'Authorization': f'Bearer {api_key}',
        'Content-Type': 'application/json'
    }

    # 请求数据
    data = {
        "inputs": {},
        "query": query,
        "response_mode": "streaming", #blocking streaming
        "conversation_id": "",
        "user": "abc-123",
            }

    # 将数据转换为JSON字符串
    data_json = json.dumps(data)

    # 发送POST请求
    # response = requests.post(url, headers=headers, data=data_json.encode("utf-8"))
    # response.encoding = 'utf-8'

    # answer = ''
    st = time.time()
    # 发送GET请求，并设置stream=True
    with requests.post(url, headers=headers, data=data_json.encode("utf-8"), stream=True) as r:
        # 检查请求是否成功
        r.raise_for_status()

        # 打印每一行文本
        for line in r.iter_lines():
            # 忽略保持连接的空行
            if line:
                # 解码每一行，因为返回的数据可能是字节串
                line = line.decode('utf-8')
                # print(line)
                if 'data: ' in line:
                    data = json.loads(line.replace('data: ', ''))
                    if data['event'] == 'message':
                        # print(data['answer'])
                        answer = data['answer']
                        yield answer
                    elif data['event'] == 'message_end':
                        return
                        # sys.stdout.write(f"\r{str([answer])}")
                        # sys.stdout.flush()
    # return answer

def dify_chat1(query,conversation_id=''):
    # 生成sql
    # API端点和API密钥
    # url = 'http://172.16.32.45:11206/v1/chat-messages' #v1/chat-messages'调用聊天框  workflows/run智能体
    url = f'{DIFY_URL}/v1/chat-messages'  # v1/chat-messages'调用聊天框  workflows/run智能体
    api_key = 'app-7NJfiS9V4p7rKm5MXbNV3BcR'  # 替换为您的实际API密钥
    # api_key = 'app-9mryTeUEqXIshnD1tEOko0Rp'  # 替换为您的实际API密钥

    # 请求头
    headers = {
        'Authorization': f'Bearer {api_key}',
        'Content-Type': 'application/json'
    }

    # 请求数据
    data = {
        "inputs": {},
        "query": query,
        "response_mode": "streaming",  # blocking streaming
        "conversation_id": conversation_id,
        # "conversation_id": '',
        "user": "abc-123",
    }

    # 将数据转换为JSON字符串
    data_json = json.dumps(data)

    # 发送POST请求
    # response = requests.post(url, headers=headers, data=data_json.encode("utf-8"))
    # response.encoding = 'utf-8'

    # answer = ''

    # 发送GET请求，并设置stream=True
    with requests.post(url, headers=headers, data=data_json.encode("utf-8"), stream=True) as r:
        # 检查请求是否成功
        r.raise_for_status()

        # 打印每一行文本
        for line in r.iter_lines():
            # 忽略保持连接的空行
            if line:
                # 解码每一行，因为返回的数据可能是字节串
                line = line.decode('utf-8')
                # print(line)
                if 'data: ' in line:
                    data = json.loads(line.replace('data: ', ''))
                    if data['event'] == 'message':
                        # print(data['answer'])
                        answer = data['answer']
                        conversation_id = data['conversation_id']
                        task_id = data['task_id']
                        # sys.stdout.write(f"\r{str([task_id])}")
                        # sys.stdout.flush()
                        yield f"{task_id}\t{conversation_id}\t{answer}"
                    elif data['event'] == 'message_end':
                        return
                        # answer += data['answer']
                        # sys.stdout.write(f"\r{str([answer])}")
                        # sys.stdout.flush()

    # return answer, conversation_id
    # return conversation_id

#停止输出
def dify_stop(task_id):

    #任务id为空
    if not task_id:
        return False

    url = f'{DIFY_URL}/v1/chat-messages/{task_id}/stop'  # v1/chat-messages'调用聊天框  workflows/run智能体
    api_key = 'app-7NJfiS9V4p7rKm5MXbNV3BcR'  # 替换为您的实际API密钥

    # 请求头
    headers = {
        'Authorization': f'Bearer {api_key}',
        'Content-Type': 'application/json'
    }

    # 请求数据
    data = {
        "user": "abc-123",
    }

    # 将数据转换为JSON字符串
    data_json = json.dumps(data)

    response = requests.post(url, headers=headers, data=data_json.encode("utf-8"), stream=True)

    if response.status_code == 200:
        return True
    else:
        return False

import pymysql
import csv
from datetime import datetime
def db_connet():
    # conn = pymysql.connect(
    #     host='127.0.0.1',  # 连接名称，默认127.0.0.1
    #     user='root',  # 用户名
    #     passwd='123456',  # 密码
    #     port=3306,  # 端口，默认为3306
    #     db='zentao',  # 数据库名称
    #     charset='utf8',  # 字符编码
    # )
    conn = pymysql.connect(**PYMYSQL_CONF)
    print("----数据库连接成功------")
    # logging.info("----数据库连接成功------ %s", conn)
    return conn
def search_db():
    import datetime

    # 获取当前时间
    now = datetime.datetime.now()
    f_path = './data_out/'
    if not os.path.exists(f_path):
        os.mkdir(f_path)
    # 将时间格式化为字符串，例如：2025-03-11_14-30-00
    file_name = now.strftime(f_path + "output_%Y-%m-%d_%H-%M-%S.txt")

    # 使用格式化后的文件名打开文件
    file = open(file_name, 'w', encoding='utf-8')
    # file=open('./data_out/output.txt', 'w', encoding='utf-8')
    # 获取游标
    conn = db_connet()
    cursor = conn.cursor()

    # 指定要查询的表格列表
    # table_names = [
    #     'ex_project',
    #     'ex_file'
    # ]
    table_names = {
        'ex_project': '公司业绩基本信息表',
        'ex_file': '附件信息表',
        "ex_bid": " ",
        "ex_bidPmProject": '',
        "ex_bidProject": '',
        "ex_bidSocialInsurance": '',
        "ex_bidTeam": '',
        "ex_company": '',
        "ex_dept": '',
        "ex_team": "",
        "ex_teamProject": '',
        "ex_teamQualification": '',
        "ex_teamWork": ''

    }

    # 构造SQL查询，获取指定表的表名、字段名和字段备注
    # sql = """
    # SELECT TABLE_NAME, COLUMN_NAME, COLUMN_COMMENT
    # FROM INFORMATION_SCHEMA.COLUMNS
    # WHERE TABLE_SCHEMA = 'zentao' AND TABLE_NAME IN ({})
    # """.format(','.join(['%s'] * len(table_names))
    # )

    sql = """
        SELECT 
        t.TABLE_NAME, 
        t.TABLE_COMMENT, 
        c.COLUMN_NAME, 
        c.COLUMN_COMMENT,
        k.REFERENCED_TABLE_NAME, 
        k.REFERENCED_COLUMN_NAME,
        k.CONSTRAINT_NAME
    FROM 
        INFORMATION_SCHEMA.TABLES t
    JOIN 
        INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
    LEFT JOIN 
        INFORMATION_SCHEMA.KEY_COLUMN_USAGE k ON c.TABLE_NAME = k.TABLE_NAME 
        AND c.COLUMN_NAME = k.COLUMN_NAME AND c.TABLE_SCHEMA = k.TABLE_SCHEMA
        AND k.REFERENCED_TABLE_NAME IS NOT NULL
    WHERE 
        t.TABLE_SCHEMA = 'zentao' AND t.TABLE_NAME IN ({})

        """.format(','.join(['%s'] * len(table_names)))

    try:
        # 执行SQL查询
        cursor.execute(sql, list(table_names.keys()))
        results = cursor.fetchall()
        # print("results:",results) #tuple  (('zt_task', 'id', '编号'), ('zt_task', 'project', '项目'),
        dict_tem = {}
        for table_name, table_comment, tb_colum, colum_comment, ref_table, ref_column, constraint_name in results:
            if table_name not in dict_tem:
                dict_tem[table_name] = {
                    'table_comment': table_comment,
                    'columns': [],
                    'foreign_keys': []
                }
            dict_tem[table_name]['columns'].append(f"{tb_colum}({colum_comment})")
            if ref_table:
                # dict_tem[table_name]['foreign_keys'].append({
                #     'constraint_name': constraint_name,
                #     'column_name': tb_colum,
                #     'referenced_table': ref_table,
                #     'referenced_column': ref_column
                # })
                dict_tem[table_name]['foreign_keys'].append({
                    '约束名称': constraint_name,
                    '字段名称': tb_colum,
                    '引用表名': ref_table,
                    '引用字段': ref_column
                })
        # print(dict_tem)
        logging.info('dict_tem:%s', dict_tem)
        for key, values in dict_tem.items():
            file.writelines(key + "（" + values["table_comment"] + "）" + '\n')
            file.writelines('表字段：' + ','.join(values["columns"]) + '\n')
            for dict in values["foreign_keys"]:
                file.writelines('表外键：' + str(dict) + '\n')
            file.writelines('\n')
    finally:
        # 关闭游标和连接
        cursor.close()
        conn.close()
    return dict_tem
def write_json(filename, new_data):
    # 读取现有数据
    data = read_json(filename)

    # 检查新数据是否已存在于现有数据中
    for new_item in new_data:
        if new_item not in data:
            data.append(new_item)

    # 将数据转换为JSON格式并写入文件
    with open(filename, 'w', encoding='utf-8') as file:
        json.dump(data, file, ensure_ascii=False, indent=4)


def read_json(filename):
    # 检查文件是否存在，并读取现有数据（如果存在）
    if os.path.exists(filename):
        with open(filename, 'r', encoding='utf-8') as file:
            try:
                data = json.load(file)
            except json.JSONDecodeError:
                data = []  # 如果文件不是有效的JSON，则开始一个新的列表
    else:
        data = []  # 如果文件不存在，则开始一个新的列表

    return data
def data_base_connet(sql_statement):
    # 获取游标
    conn=db_connet()
    cursor = conn.cursor()
    # print(cursor)

    # 执行sql语句execute和executemany
    # 定义要执行的SQL语句列表
    try:
        cursor.execute(sql_statement)
        hearders = [description[0] for description in cursor.description]
        results = cursor.fetchall()

        # print(results)

        cursor.close()  # 关闭查询游标
        conn.commit()  # 事务的提交
        conn.close()  # 查询完毕，需要关闭连接，释放计算机资源
        # print('sql执行成功')

        return hearders, results
    except pymysql.MySQLError as e:
        print(f"An error occurred: {e}")
        cursor.close()  # 关闭查询游标
        conn.commit()  # 事务的提交
        conn.close()  # 查询完毕，需要关闭连接，释放计算机资源
        # print('sql执行成功')
        return e,None

import requests
import json
import re
def re_str(str):
    # 调整正则表达式以包含换行符和任意空白字符
    # pattern_including_newline = r'(.*?)\s*表字段'
    pattern_including_newline = r'(.*?)\s*（'
    # 使用re.DOTALL标志，使点号匹配包括换行符在内的任意字符
    match_including_newline = re.search(pattern_including_newline, str, re.DOTALL)

    # 提取匹配的内容
    result_including_newline = match_including_newline.group(1).strip() if match_including_newline else None
    return result_including_newline
def dify_searchdb():
    dict_new={}
    api_key = 'dataset-4ZAxt6Z22ks6Z6aECTqFbZ2p'  # 替换为您的实际API密钥
    # dataset_id="b0b2a031-6fa8-4cb6-a4a5-b45da8754aa5"
    # document_id = 'a04e8796-bd6d-4c76-9bed-5486942472ac'

    dataset_id="e585f1aa-88ff-49e1-b5cf-c88aed41d4dd"
    document_id='b1248886-a409-4b59-b87d-0a60accfa695'
    url =  f'{DIFY_URL}/v1/datasets/{dataset_id}/documents/{document_id}/segments'  # v1/chat-messages'调用聊天框  workflows/run智能体

    headers = {
        'Authorization': f'Bearer {api_key}',
        'Content-Type': 'application/json'
    }

    # 发送POST请求
    response = requests.get(url, headers=headers)
    response_data=json.loads(response.text)
    s=response_data.get('data')
    for i in s:
        for key,value in i.items():
            # print('key:',key,'values:',value)
            # if key=='position':
            #     # print('key:',key,'values:',value)
            if key=='content':
                # print('key:', key, 'values:', value)
                v_str=re_str(value)
                # print('v_str:',v_str)
                dict_new[v_str]=value

    return response,dict_new

#sql转为markdown格式
def sql2md(columns, data, conversation_id):
    if len(columns) == 0 or len(data) == 0:
        return f"\n未查询到数据"

    # 开始构建Markdown表格
    md_table = '| ' + ' | '.join(columns) + ' |\n'
    md_table += '| ' + ' | '.join(['---'] * len(columns)) + ' |\n'

    # 添加每一行数据
    for row in data:
        row_fillna = []
        for i in row:
            if i:
                row_fillna.append(str(i))
            else:
                row_fillna.append('/')
        md_table += '| ' + ' | '.join(field for field in row_fillna) + ' |\n'
        # md_table += '| ' + ' | '.join(str(field) for field in row) + ' |\n'

    return f"sql查询结果如下：\n{md_table}\n"

def dify_chat2(query, api_key,conversation_id='', llm='deepseek'):
    # API端点和API密钥
    # url = 'http://172.16.32.45:11206/v1/chat-messages' #v1/chat-messages'调用聊天框  workflows/run智能体
    # url = 'http://192.168.41.:8083/v1/chat-messages'  # v1/chat-messages'调用聊天框  workflows/run智能体
    # url = f'{DIFY_URL}/v1/chat-messages'  # v1/chat-messages'调用聊天框  workflows/run智能体
    url = f'{DIFY_URL}/v1/chat-messages'  # v1/chat-messages'调用聊天框  workflows/run智能体
    # if llm == 'deepseek':
    #     api_key = 'app-E5OKzE323dg4s4g8T8lwmG40'  # 项目分类'app-DtzcELntHBvJ8L8IwAatVZHG'  # 替换为您的实际API密钥
    # else:
    #     api_key = 'app-bBx6yIAQL9LzoBKjDsKOVuxY'

    # 请求头
    headers = {
        'Authorization': f'Bearer {api_key}',
        'Content-Type': 'application/json'
    }

    # 请求数据
    data = {
        "inputs": {},
        "query": query,
        "response_mode": "streaming",  # blocking streaming
        "conversation_id": conversation_id,
        "user": "abc-123",
    }

    # 将数据转换为JSON字符串
    data_json = json.dumps(data)

    # 发送POST请求
    # response = requests.post(url, headers=headers, data=data_json.encode("utf-8"))
    # response.encoding = 'utf-8'

    answer = ''

    # 发送GET请求，并设置stream=True
    with requests.post(url, headers=headers, data=data_json.encode("utf-8"), stream=True) as r:
        # 检查请求是否成功
        r.raise_for_status()

        # 打印每一行文本
        for line in r.iter_lines():
            # 忽略保持连接的空行
            if line:
                # 解码每一行，因为返回的数据可能是字节串
                line = line.decode('utf-8')
                print(line)
                if 'data: ' in line:
                    data = json.loads(line.replace('data: ', ''))
                    if data['event'] == 'message':
                        # print(data['answer'])
                        answer = data['answer']
                        conversation_id = data['conversation_id']
                        yield f"{conversation_id}\t{answer}"
                    elif data['event'] == 'message_end':
                        return
    # return answer, conversation_id

def slice_text(text, slice_length):
    slices = []
    slices_new = []
    current_slice = ""
    current_length = 0
    title = ''
    if ':' in text:
        title = text.split('：')[0]
    for char in text:
        current_slice += char
        current_length += 1

        # 当当前切片长度接近1000时，开始寻找分割点
        if current_length >= slice_length:
            # 查找最近的）》号或上一个标点符号
            split_point = re.search(r'》|[^，。！？；：]', current_slice[::-1])
            if split_point:
                # 获取分割点的真实位置
                split_index = len(current_slice) - split_point.start()
                # 添加切片到列表
                slices.append(current_slice[:split_index])
                # 重置当前切片和长度计数器
                current_slice = current_slice[split_index:]
                current_length = len(current_slice)

    # 添加最后一个切片
    if current_slice:
        slices.append(current_slice)
    for i, slice in enumerate(slices):
        if i == 0:
            print(f"切片{i + 1}：长度为：{len(slice)}{slice}\n")
            slices_new.append(slice)
        else:
            print(f"切片{i + 1}：长度为：{len(slice)}{title + slice}\n")
            slices_new.append(title + slice)
    return slices_new


def split_list_into_chunks(lst, chunk_size=5000):
    """
    Splits a list of strings into chunks of a specified size, ensuring that individual strings are not split.

    :param lst: List of strings to be split.
    :param chunk_size: Maximum size of each chunk.
    :return: List of chunks.
    """
    chunks = []
    current_chunk = []

    for item in lst:
        # If adding this item to the current chunk exceeds the chunk size, start a new chunk
        if len(item) > chunk_size:
            print('长度超长了：', len(item))
            slices = slice_text(item, chunk_size)
            for s_i in slices:
                current_chunk.append(s_i)
        if sum(len(x) for x in current_chunk) + len(item) > chunk_size:
            chunks.append(''.join(current_chunk))
            current_chunk = [item]
        else:
            current_chunk.append(item)

    # Add the last chunk if it's not empty
    if current_chunk:
        chunks.append(''.join(current_chunk))

    return chunks

#意图识别
def intention_recognition(user_input, api_key):
    conversation_id = ''
    data_answ = ''
    # while True:
    # user_input = input('用户输入问题：')
    data_answ = ''
    prompt = '''
 ##功能：用户意图识别，不管用户输入什么都只做意图识别。
    '''
    # while '服务走丢了' in data_answ or data_answ == '':
    # data_answ, conversation_id = dify_chat2(query='用户问题为：' + user_input + '\n提示词为：' + prompt,
    #                                         conversation_id=conversation_id, llm='dawate')
    llm_input = '用户问题为：' + user_input + '\n提示词为：' + prompt
    stream_content = dify_chat2(query=llm_input,api_key=api_key,conversation_id=conversation_id, llm='deepseek')
    data_answ = ''
    try:
        while True:
            gen_text = next(stream_content)
            gen_text_split = gen_text.split('\t')
            conversation_id = gen_text_split[0]
            data_answ += '\t'.join(gen_text_split[1:])
            # sys.stdout.write(f"\r {[data_answ]}")
            # sys.stdout.flush()

    except StopIteration as e:
        # conversation_id = e.value
        pass
    print('输出结果为：', data_answ)
    pattern = r"```json(.*?)```"
    matches = re.findall(pattern, data_answ.split('</think>')[-1], re.DOTALL)
    json_statement = json.loads(matches[-1])
    # print('意图：', json_statement.get('意图'))

    return json_statement.get('业务域意图'),json_statement.get('提示语')

def ai_qa_gen(dict_tem, query, sql_md, conversation_id, results):
    # 意图识别
    stream_content = dify_chat2(query=query, api_key='app-nktbM87dvmYyrIibgbRB2or6', conversation_id=conversation_id, llm='deepseek')
    data_answ = ''
    try:
        while True:
            gen_text = next(stream_content)
            gen_text_split = gen_text.split('\t')
            conversation_id = gen_text_split[0]
            answer = '\t'.join(gen_text_split[1:])
            data_answ += answer
            print(answer, end='')
            yield answer

    except StopIteration as e:
        # conversation_id = e.value
        pass

    pattern = r"```json(.*?)```"
    matches = re.findall(pattern, data_answ.split('</think>')[-1], re.DOTALL)
    json_statement = json.loads(matches[-1])
    return ''

    # t = time.time()
    # response, dict_tem = dify_searchdb()  # 知识库已经存在 通过文本创建文档
    # print('dict_new:', dict_tem)

    # print('----------大模型提示词：---------', str(dict_tem) + str(query))
    # logging.info('----------大模型提示词：---------%s', str(query))
    # print('------用户需要查询的问题为：\n', query)
    # logging.info('------用户需要查询的问题为：\n%s', query)
    # data_answ=dify_chat(query,str(dict_tem)+str(table_connect))
    # data_answ = ""
    # count = 0  # 初始化计数器
    st = time.time()
    # while data_answ == "":
    #     if count > 0:
    #         yield '\n第一次查询失败，尝试第二次查询\n'
    #     data_answ = dify_chat_streaming(query)  # 找出表
    #     count += 1
    #     if count == 3:
    #         #查找三次
    #         break
    # yield f'\t{conversation_id}\t查询数据表中\n'
    data_answ = ''
    stream_content = dify_chat_streaming(query)  # 找出表
    try:
        while True:
            gen_text = next(stream_content)
            # gen_text = gen_text.replace('<think>','<div style="background-color:lightcyan;">').replace('</think>','</div>')
            gen_text = gen_text.replace('<think>','<div style="color: #8b8b8b;">').replace('</think>','</div>')
            data_answ += gen_text
            # yield f"\t{conversation_id}\t{gen_text}"
    except StopIteration as e:
        # conversation_id = e.value
        pass

    print(time.time()-st,[data_answ])
    list_data = {}  # 需要的表结构

    if '```json' not in data_answ:
        if not sql_md:  # 检查是否达到三次且依旧没有找到表，则直接聊天
            yield f'\t{conversation_id}\t\n'
            stream_content = dify_chat1(query, conversation_id)  # 编写sql
            try:
                while True:
                    gen_text = next(stream_content)
                    gen_text_split = gen_text.split('\t')
                    conversation_id = gen_text_split[1]
                    # yield gen_text.replace('<think>','```思考中').replace('</think>','```')
                    # yield gen_text.replace('<think>','<div style="background-color:lightcyan;">').replace('</think>','</div>')
                    yield gen_text.replace('<think>','<div style="color: #8b8b8b;">').replace('</think>','</div>')
            except StopIteration as e:
                # conversation_id = e.value
                pass

            #此事件为聊天，直接返回
            return conversation_id, sql_md, results
    else:
        pattern1 = r"```json(.*?)```"
        # match = re.search(pattern1, data_answ, re.DOTALL)
        # json_statement = match.group(1).strip()
        matches = re.findall(pattern1, data_answ.split('</think>')[-1], re.DOTALL)
        json_statement = matches[-1]
        # print('找到的相关的表有：', json_statement)
        # else:
        #     print('不存在相关的表格。', data_answ)
        #     sql_data_answ, conversation_id = dify_chat1(str(query),conversation_id)  # 编写sql
        #     print(sql_data_answ)
        #     sys.exit()
        for key, value in json.loads(json_statement).items():
            # list_data[key.lower()]=dict_tem[key.lower()]
            if key in dict_tem.keys():
                list_data[key] = dict_tem[key]


        # print("请重试")  #
    # print("data_answ:", data_answ)
    yield f'\t{conversation_id}\t\n'

    print("-------step1:大模型找出的表结果：\n", data_answ)
    # logging.info("-------step1:大模型回答结果：\n %s", str(data_answ))

    # print("list_data:", list_data)

    # sql_data_answ,conversation_id = dify_chat1(f'表结构：\n{list_data}\n{sql_md}问题：\n{query}',conversation_id)  # 编写sql
    #查找sql
    stream_content = dify_chat1(f'表结构：\n{list_data}\n{sql_md}问题：\n{query}',conversation_id)  # 编写sql
    # stream_content = dify_chat1(f'{sql_md}问题：\n{query}',conversation_id)  # 编写sql
    sql_data_answ = ""
    try:
        while True:
            gen_text = next(stream_content)
            gen_text_split = gen_text.split('\t')
            answer = '\t'.join(gen_text_split[2:])
            conversation_id = gen_text_split[1]
            sql_data_answ += answer
            # yield gen_text.replace('<think>','```思考中').replace('</think>','```')
            # yield gen_text.replace('<think>','<div style="background-color:lightcyan;">').replace('</think>','</div>')
            yield gen_text.replace('<think>','<div style="color: #8b8b8b;">').replace('</think>','</div>')
    except StopIteration as e:
        # conversation_id = e.value
        pass

    pattern = r"```sql(.*?)```"
    # match = re.search(pattern, sql_data_answ, re.DOTALL)
    matches = re.findall(pattern, sql_data_answ.split('</think>')[-1], re.DOTALL)
    # user_questions = [{}]

    # hearders, results = [[],[]]
    sql_md = ""
    results = []

    if len(matches) != 0:

        sql_statement = matches[-1]

        #简单判断sql和投标是否相关，不相关直接结束
        if 'ex_' not in sql_statement:
            return conversation_id, sql_md, results

        yield f"\t{conversation_id}\t\n查询到sql语句，正在执行中...\n"
        # sql_statement = match.group(1).strip()

        print("\n-------step2查到的对应的sql语句为：\n", sql_statement)
        # logging.info("\n-------step2查到的对应的sql语句为：\n%s", sql_statement)
        hearders, results = data_base_connet(sql_statement)
        # print(len(results),results)
        if results is not None:
            # user_questions[0]['问题'] = query
            # user_questions[0]['SQL'] = sql_statement
            # print('\n-------step3:sql执行结果：\n')
            # logging.info('\n-------step3:sql执行结果：\n')
            # print("results:\n",results)
            # logging.info('%s', hearders)
            # print(hearders)
            # for i in results:
            #     print(i)
            #     logging.info(i)
            #转为md文本
            sql_md = sql2md(hearders, results,conversation_id)
            yield f"\t{conversation_id}\t\n{sql_md}"

        else:
            print("------sql生成错误正在重新生成-----")
            yield f"\t{conversation_id}\t\n------sql生成错误正在重新生成-----\n"
            # logging.info("------sql生成错误-----")
            sql_error = hearders
            # sql_data_answ,conversation_id = dify_chat1('表结构：' + str(list_data) + '问题：' + str((query)) + str(sql_error),conversation_id)
            # sql_data_answ,conversation_id = dify_chat1(f"sql语句执行报错，错误如下：\n{sql_error}",conversation_id)

            # 查找sql
            stream_content = dify_chat1(f"sql语句执行报错，错误如下：\n{sql_error}",conversation_id)  #第二次生成
            sql_data_answ = ""
            try:
                while True:
                    gen_text = next(stream_content)
                    gen_text_split = gen_text.split('\t')
                    answer = '\t'.join(gen_text_split[2:])
                    conversation_id = gen_text_split[1]
                    sql_data_answ += answer
                    # yield gen_text.replace('<think>','```思考中').replace('</think>','```')
                    # yield gen_text.replace('<think>','<div style="background-color:lightcyan;">').replace('</think>','</div>')
                    yield gen_text.replace('<think>','<div style="color: #8b8b8b;">').replace('</think>','</div>')
            except StopIteration as e:
                # conversation_id = e.value
                pass

            # match = re.search(pattern, sql_data_answ, re.DOTALL)
            matches = re.findall(pattern, sql_data_answ.split('</think>')[-1], re.DOTALL)
            if len(matches) != 0:
                sql_statement = matches[-1]

                # 简单判断sql和投标是否相关，不相关直接结束
                if 'ex_' not in sql_statement:
                    return conversation_id, sql_md, results

                yield f"\t{conversation_id}\t\n查询到sql语句，正在执行中...\n"
                # sql_statement = match.group(1).strip()

                print("\n-------step2查到的对应的sql语句为：\n", sql_statement)
                # logging.info("\n-------step2查到的对应的sql语句为：\n%s", sql_statement)
                if sql_statement:
                    hearders, results = data_base_connet(sql_statement)
                    if results is not None:
                        # user_questions[0]['问题'] = query
                        # user_questions[0]['SQL'] = sql_statement
                        # print(hearders)
                        # for i in results:
                        #     print(i)
                        #     logging.info(i)
                        # 转为md文本
                        sql_md = sql2md(hearders, results,conversation_id)
                        print(sql_md)
                        yield f"\t{conversation_id}\t\n{sql_md}"

                    else:
                        print('\nSQL查询报错：', results)
                        yield f"\t{conversation_id}\t\nSQL查询报错，请优化提示词"

    return conversation_id, sql_md, results

    # 将sql暂时保存到本地
    # 创建目录（如果不存在）
    # if not os.path.exists('out_json/'):
    #     os.mkdir('out_json/')

    # filename = 'out_json/user_questions.json'
    # print(f'耗时：{time.time() - t}')
    # data = read_json(filename)
    # print("现有数据:", data)
    #
    # # 添加新数据并写入文件
    # write_json(filename, user_questions)
    #
    # # 再次读取并打印数据
    # data = read_json(filename)
    # print("更新后的数据:", data)

    # '''
    # 说明：该版本 优化：
    # 1、知识库接口新建
    # 2、将内容以接口直接传到dify知识库
    # 以上只有第一次需要
    #
    # 后续直接调用知识库即可：
    # 调用一次后直接缓存，后续无需重复调用知识库内容筛选，避免超时；
    # 3、从dify知识库直接获取内容进行筛选
    #
    # '''

    # return conversation_id, hearders, results

if __name__=="__main__":
    print(1)

    # conversation_id = ''
    # sql_md = ""
    # results = []
    # response, dict_tem = dify_searchdb()  # 知识库已经存在 获取表结构
    #
    # while True:
    #
    #     query = input("\n请输入你的问题：")
    #
    #     # 先查看是否为生成指令
    #     query_type = generate_type(query)
    #
    #     if query_type == '其他':
    #
    #         # 将sql数据转为md
    #         # sql_md = sql2md(hearders, results,conversation_id)
    #         # conversation_id, sql_md, results = main(query, sql_md, conversation_id)
    #         stream_content = ai_qa_gen(dict_tem, query, sql_md, conversation_id)
    #         sql_data_answ = ""
    #         try:
    #             while True:
    #                 answer = next(stream_content)
    #                 sql_data_answ += answer
    #                 sys.stdout.write(f"\r{str([sql_data_answ])}")
    #                 sys.stdout.flush()
    #         except StopIteration as e:
    #             conversation_id, sql_md, results = e.value
    #
    #     else:
    #
    #         if len(results) == 0:
    #             print('还未选择数据')
    #
    #         # 提取id
    #         ids = [i[0] for i in results]
    #
    #         if query_type == '公司业绩':
    #             # 生成团队管理
    #             print('生成公司业绩',ids)
    #         elif query_type == '负责人业绩':
    #             # 生成团队管理
    #             print('生成负责人业绩',ids)
    #         elif query_type == '团队管理':
    #             # 生成团队管理
    #             print('生成团队管理', ids)
    # query='帮我写一篇1000字的关于环境保护的作文'
    # stream_content = dify_chat1(query, '')  # 第二次生成
    # sql_data_answ = ""
    # cnt = 0
    # try:
    #     while True:
    #         answer = next(stream_content)
    #         task_id = answer.split('\t')[0]
    #         sql_data_answ += answer
    #         cnt += 1
    #         if cnt == 10:
    #             dify_stop(task_id)
    #         print(answer)
    # except StopIteration as e:
    #     conversation_id = e.value

    # s = ai_qa_gen('',query='生成团队管理',conversation_id='',sql_md='',results='')
    # while True:
    #     a = next(s)
    #     print(a)

